# -*- coding: utf-8 -*-
"""
Created on Thu Jan 18 10:00:33 2021

@author: Jing
"""
import pandas as pd
import pymysql

# 逐条插入数据脚本
def save_data(data,table_name):
    db = pymysql.connect(host='106.14.221.29', user='yangj', password='wuzhi', port=5308, db='quant_db')
    cursor = db.cursor()
    keys = ', '.join(data.keys())                               # keys = id, name, age
    values = ', '.join(['%s'] * len(data))                      # values = %s, %s, %s
    update = ', '.join(
        [" {key} = %s".format(key=key) for key in data]
    )                                                           # id = %s, name = %s, age = %s
    sql = '''INSERT INTO {table_name}({keys}) VALUES({values})'''.format(table_name=table_name, keys=keys, values=values)

    try:
        tuple_ = tuple(data.values())
        if cursor.execute(sql, tuple_):
            print('成功插入数据')
        db.commit()

    except:
        print('插入数据失败')
        db.rollback()

    db.close()

    return

if __name__=='__main__':
    data = {
        'date': '20210122',
        'prodname': '致远CTA陆家嘴精选1期',
        'netvalue': 1.7520
    }
    table_name = 'prod_netvalues'

    d = save_data(data,table_name)